import pymysql
from datetime import datetime

# 获取数据库连接
def get_connection():
    host = '127.0.0.1'
    port = 3306
    user = 'root'
    password = '123456'
    database = 'virus'
    db = pymysql.connect(host, user, password, database, charset='utf8', port=port)
    return db

# 获取游标
def get_cursor(con):
    cursor = con.cursor()
    return cursor

# 关闭数据库连接
def close_connection(con):
    con.close()


# 获取身份数据
def get_provincee_day_data(con, cursor, last_updata_time):
    sql = '''
           select id, record_date, update_time
           from province_data
           where update_time = %s
           limit 1
       '''
    cursor.execute(sql, (last_updata_time,))
    # fetchall返回数据组成的元组，（如果没有数据会返回空的元组）
    # fetchone 返回一条数据的元组，但是没有数据的时候会返回None
    result = cursor.fetchall()
    return result


# 删除指定省份的数据
def delete_province_data(con, cursor, record_data):
    sql = '''
             delete from province_data
            where record_date = %s
        '''
    cursor.execute(sql, (record_data,))
    con.commit()


# 删除指定日期的数据
def delete_day_data(con, cursor,record_data):
    sql = '''
         delete from total_data
        where record_date = %s
    '''
    cursor.execute(sql,(record_data,))
    con.commit()

# 插入当天最新数据
def insert_today_data(con, cursor, record_data, last_update_time, china_total, china_add):
    sql = '''
     insert into total_data 
    (record_date, total_confirm, total_suspect, total_dead, total_heal, add_confirm, add_suspect, add_dead, add_heal, update_time) 
    values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    '''
    cursor.execute(sql, (record_data, china_total['confirm'], china_total['suspect'], china_total['dead'], china_total['heal'], china_add['confirm'], china_add['suspect'], china_add['dead'], china_add['heal'], last_update_time))
    con.commit()

# 查询当天的疫情统计数据
def get_day_data(con, cursor,last_updata_time):
    sql = '''
        select id, record_date, update_time
        from total_data
        where update_time = %s
        limit 1
    '''
    cursor.execute(sql,(last_updata_time,))
    result = cursor.fetchall()
    return result

# 保存疫情统计数据
def save_day_data(con, cursor, day_total):
    sql = 'insert into total_data (record_date, total_confirm, total_suspect, total_dead, total_heal, update_time) values (%s, %s, %s, %s, %s, %s)'
    cursor.execute(sql, ('2020-' + day_total['date'].replace('.', '-'), day_total['confirm'], day_total['suspect'], day_total['dead'], day_total['heal'], datetime.now()))
    con.commit()

# 更新疫情数据
def update_day_data(con, cursor, day_add):
    sql = 'update total_data set add_confirm = %s, add_suspect = %s, add_dead = %s, add_heal = %s, update_time = %s where record_date = %s'
    cursor.execute(sql, (day_add['confirm'], day_add['suspect'], day_add['dead'], day_add['heal'], datetime.now(), '2020-' + day_add['date'].replace('.', '-')))
    con.commit()    

# 保存区域疫情数据
def save_province_data(con, cursor, record_date, province,last_updata_time):
    sql = 'insert into province_data (record_date, province_name, total_confirm, update_time) values (%s, %s, %s, %s)'
    cursor.execute(sql, (record_date, province['name'], province['total']['confirm'], last_updata_time))
    con.commit()       









